/*
 * IC Project - a prototype to test a hypothesis of collective intelligence
 * Copyright (C) 2012 Paolo Conte - paolo@concavi.net
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.ix.data.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.ix.data.entities.HNode;
import org.ix.data.manager.POJOManager;

public class Types {

    private transient Connection cnn = null;
    private final String basequery = "SELECT TPKID, TDESCR, FKTYPE, HLEVEL FROM TYPES";

    public Types(Connection cnn) {
        this.cnn = cnn;
    }

    public synchronized ArrayList<Type> loadTypes(Type filter) throws SQLException {
        String query = basequery;
        String where = POJOManager.createWhereClause(filter);

        if (where.length() > 0) {
            query += " WHERE " + where.toString();
        }

        ArrayList types = new ArrayList(300);

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, filter);
        ResultSet rset = prstmt.executeQuery();

        while (rset.next()) {
            Type type = new Type();
            try {
                type = (Type) POJOManager.loadObject(rset, type);
                //System.out.println(type);
                types.add(type);
            } catch (Exception genexc) {
                Logger.getLogger(Types.class.getName()).log(Level.SEVERE, null, genexc);
            }
        }

        POJOManager.close(rset, prstmt);

        return types;
    }

    public synchronized Type loadType(Type filter) throws SQLException {
        String query = basequery;
        String where = POJOManager.createWhereClause(filter);

        if (where.length() > 0) {
            query += " WHERE " + where.toString();
        }

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, filter);
        ResultSet rset = prstmt.executeQuery();

        Type type = null;
        if (rset.next()) {
            type = new Type();
            try {
                type = (Type) POJOManager.loadObject(rset, type);
            } catch (Exception genexc) {
                Logger.getLogger(Types.class.getName()).log(Level.SEVERE, null, genexc);
            }
        }

        POJOManager.close(rset, prstmt);

        return type;
    }

    public synchronized int deleteTypes(Type filter) throws SQLException {
        String query = "DELETE FROM TYPES";
        String where = POJOManager.createWhereClause(filter);

        if (where.length() == 0) {
            return 0;   // unable to delete all types
        }

        query += " WHERE " + where.toString();

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, filter);
        int rows = prstmt.executeUpdate();

        POJOManager.close(prstmt);

        return rows;
    }

    public synchronized int updateType(Type type) throws SQLException {
        String query = "UPDATE TYPES"
                + " SET TDESCR = ?, FKTYPE = ?, HLEVEL = ?";

        String where = POJOManager.createWhereClause(type);
        if (where.length() == 0) {
            return 0;   // unable to update all types
        }

        query += " WHERE " + where.toString();

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, type);

        int rows = prstmt.executeUpdate();

        POJOManager.close(prstmt);

        return rows;
    }

    public synchronized int insertType(Type type) throws SQLException {
        String query = "INSERT INTO TYPES (TDESCR, FKTYPE, HLEVEL)"
                + " VALUES (?,?,?) ";

        if (type.fktype == null) {
            type.fktype = new Long(0);
        }
        if (type.hlevel == null) {
            type.hlevel = new Integer(0);
        }

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, type);

        int rows = prstmt.executeUpdate();

        POJOManager.close(prstmt);

        return rows;
    }

    public synchronized HNode loadHType(Type filter) throws SQLException {
        return loadHType(null, filter);
    }

    public synchronized HNode loadHType(HNode node, Type filter) throws SQLException {

        ArrayList<Type> types = loadTypes(filter);
        Iterator<Types.Type> iterTypes = types.iterator();
        while (iterTypes.hasNext()) {
            Types.Type type = iterTypes.next();
            //Type type = loadType(filter);
            System.out.println("->" + type);
            if (type == null) {
                continue;
            }
            HNode tmpNode = new HNode(type.tdescr, type.tdescr + " (" + type.tpkid + "-" + type.fktype + ")");
            if (node == null) {
                node = tmpNode;
            } else {
                node.addChild(tmpNode);
            }
            Type childType = new Type();
            childType.fktype = type.tpkid;
            node = loadHType(node, childType);
        }
        return node;
    }

    public static class Type {

        public Long tpkid = null;
        public String tdescr = null;
        public Long fktype = null;
        public Integer hlevel = null;

        public String toString() {
            return "Type[" + tpkid + "] " + tdescr + " (" + fktype + ")";
        }
    }
}
